# -*- coding: utf-8 -*-

__author__ = 'gongqf'
from contextlib import closing
import sqlite3,os
from flask import Flask, render_template, g, request, session, jsonify, current_app, redirect, url_for,flash, json
from datetime import datetime
from math import ceil

DATABASE = r'res3.db3'
DEBUG=True
SECRET_KEY='development key'
USERNAME='admin'
PASSWORD='default'

app = Flask(__name__)
app.config.from_object(__name__)

def init_db():
    with closing(connect_db()) as db:
        with app.open_resource('schema.sql') as f:
            db.cursor().executescript(f.read())
        db.commit()

def query_db(query, args=(), one=False):
#    g.db.execute("PRAGMA foreign_keys = ON")
    cur = g.db.execute(query, args)
    rv = [dict((cur.description[idx][0], value)
        for idx, value in enumerate(row)) for row in cur.fetchall()]
    return (rv[0] if rv else None) if one else rv

def insert(table, fields=(), values=(),usenone=True):
    # g.db is the database connection
    cur = g.db.cursor()
    query = 'INSERT INTO %s (%s) VALUES (%s)' % (
        table,
        ', '.join(fields),
        ', '.join(['?'] * len(values))
        )
    print query,values
    if usenone:
        v=[]
        for x in values:
            if x=='':
                v.append(None)
            else:
                v.append(x)
    values=tuple(v)
    cur.execute(query, values)
    g.db.commit()
    id = cur.lastrowid
    cur.close()
    return id

def update(table,pk,pv,fields=(),values=(),usenone=True):
    cur = g.db.cursor()
    f=[]
    [f.append('%s=?'%x) for x in fields]
    query = 'update %s set %s where %s' % (
        table,
        ', '.join(f),
        '%s=?'%pk
        )
    if usenone:
        v=[]
        for x in values:
            if x=='':
                v.append(None)
            else:
                v.append(x)
        v.append(pv)
    values=tuple(v)
    print query,values
    cur.execute(query, values)
    g.db.commit()
    id = cur.lastrowid
    cur.close()
    return id


def connect_db():
    return sqlite3.connect(app.config['DATABASE'])

@app.before_request
def before_request():
    g.db = connect_db()
    g.db.execute("PRAGMA foreign_keys = ON")

@app.teardown_request
def teardown_request(exception):
    if hasattr(g, 'db'):
        g.db.close()

@app.route('/')
def index():
    empes=query_db("select EmpeID,fullname as EmpeName from tempe where areaid is not null")
    areas=query_db("select AreaID,AreaName from tarea")
    return render_template('index-dizh.html',empes=empes,areas=areas)

@app.route('/json_addr')
def json_addr():
    term = request.args.get('term', '', type=str)
    sql="select addrid as id,address||'&nbsp;&nbsp;&nbsp;&nbsp;<span style=''float:right''>'||commname||'</span>' as label,address as value from vaddr where address like '%%%s%%' limit 10"%term
    #    sql="select PortID,PortName,PortNameEn,CountryName from vcountryport where portnameen like '%%%s%%' limit 10"%port.upper()
    print sql
    r=query_db(sql)
    #    print r
    return jsonify(result=r)

@app.route('/json_comm')
def json_comm():
    term = request.args.get('term', '', type=str)
    sql="select commid as id,projectname||'&nbsp;&nbsp;&nbsp;&nbsp;<span style=''float:right''>'||ifnull(areaname,'')||'</span>' as label,projectname as value from vcatvcomm where projectname like '%%%s%%' limit 10"%term
    #    sql="select PortID,PortName,PortNameEn,CountryName from vcountryport where portnameen like '%%%s%%' limit 10"%port.upper()
    print sql
    r=query_db(sql)
    #    print r
    return jsonify(result=r)


def get_comms():
    comms=query_db("select CommID,ProjectName as CommName from tComm left join tProject on tComm.Project=tProject.ProjectID where tComm.AccessType=3")
    return comms

def get_devices():
    devices=query_db("select * from vDevice where devctypeid in (4,11)")
    return devices

def get_areas():
    areas=query_db("select * from tArea")
    return areas

def get_districts():
    districts=query_db("select * from tDistrict")
    return districts

@app.route('/_json_comms')
def _json_comms():
    areaid=request.args.get('areaid',None,type=int)
    print areaid
    comms=query_db("select * from vCatvComm where AreaID=?",(areaid,))
    return jsonify(result=comms)

@app.route('/_json_parentress')
def _json_parentress():
    commid=request.args.get('commid',None,type=int)
    print commid
    parentress=query_db("select vres.*,pvres.[InstallAddress] as pInstallAddress,pvres.[DeviceType] as pDeviceType,pvres.[DeviceName] as pDeviceName from vres left join vres as pvres on vres.[ParentRes]=pvres.resid where vres.community=? and vres.devctypeid in(4,11)",(commid,))
    return jsonify(result=parentress)

@app.route('/_json_os')
def _json_os():
    commid=request.args.get('commid',None,type=int)
    print commid
    oss=query_db("select * from vres where vres.community=? and vres.devctypeid in(4)",(commid,))
    return jsonify(result=oss)

@app.route('/_json_amp')
def _json_amp():
#    查找某个光站下的所有放大器资源
    resid=request.args.get('resid',None,type=int)
    print resid
#    amps=query_db("select * from vres where vres.community=? and vres.devctypeid in(11)",(commid,))
    amps=query_db("select * from vRes where ParentRes=? and DevcTypeID=11",(resid,))
#    print amps
    ampss=[]
    def f(rs):
        if rs:
            ampss.extend(rs)
            for r in rs:
                f(query_db("select * from vRes where ParentRes=? and DevcTypeID=11",(r['ResID'],)))
    f(amps)

#    print ampss
    return jsonify(result=ampss)

@app.route('/_json_get_allres7')
def _json_get_allres7():
#    查找光站资源下的所有门洞（分支器）资源，包括光站下放大器下的，但不包括光站和放大器等，只需要分支器（类型7）
    resid=request.args.get('resid',None,type=int)
    ress=query_db("select * from pvRes where ParentRes=?",(resid,))
    allress=[]
    allres7=[]
    def f(rs):
        if rs:
            allress.extend(rs)
            for r in rs:
                f(query_db("select * from pvRes where ParentRes=?",(r['ResID'],)))
    f(ress)
    for res in allress:
        if res['DevcTypeID']==7:
            allres7.append(res)

#    print allres7
    return jsonify(result=allres7)


def amptree(ampid):
    tree=[int(ampid)]
    while True:
        res=query_db("select ParentRes,pDevcTypeID from pvRes where ResID=?",(ampid,),one=True)
        if res['pDevcTypeID']==4 or (res['ParentRes'] in tree):
            break
        else:
            tree.append(res['ParentRes'])
            ampid=res['ParentRes']
    print tree
    return tree


@app.route('/dizhi',methods=['GET','POST'])
def dizhi():
    if request.method=="POST":
        comm=request.form.getlist('comm')
        os=request.form.getlist('os')
        osport=request.form.getlist('osport')
        amp=request.form.getlist('amp')
        address=request.form.getlist('address')
        floor=request.form.getlist('floor')
        door=request.form.getlist('door')
        addrcate=request.form.getlist('addrcate')
        memo=request.form.getlist('memo')
        for x in xrange(len(comm)):
            if amp[x]=='':
                parentres=os[x]
            else:
                parentres=amp[x]
#            if floor[x]=='': f=None
#            if door[x]=='':d=None
#            if memo[x]=='':m=None
#            g.db.execute("insert into tres(community,osport,parentres,installaddress,memo,device) values(?,?,?,?,?,?)",(comm,osport[x],parentres,f,d,11))
            stat=query_db("select Station from tRes where ResID=?",(parentres,),one=True)
            statid=stat['Station']
            res=query_db("select ResID from tRes where InstallAddress=? and Community=? and Station=? and Device=?",(address[x],comm[x],statid,11),one=True)
            if res:
                resid=res['ResID']
                update(table='tRes',pk='ResID',pv=resid,fields=('Community','OSPort','ParentRes','InstallAddress','Memo','Device','Station','InstallDate'),values=(comm[x],osport[x],parentres,address[x],memo[x],11,statid,datetime.strftime(datetime.now(),'%Y-%m-%d')))
                flash(u'%s 资源存在，修改成功'%address[x])
            else:
                resid=insert(table='tRes',fields=('Community','OSPort','ParentRes','InstallAddress','Memo','Device','Station','InstallDate'),values=(comm[x],osport[x],parentres,address[x],memo[x],11,statid,datetime.strftime(datetime.now(),'%Y-%m-%d')))
                flash(u'%s 资源新增成功'%address[x])
            addr=query_db("select AddrID from tAddr where Address=?",(address[x],),one=True)
            if addr:
                addrid=addr['AddrID']
                update(table='tAddr',pk='AddrID',pv=addrid,fields=('Floor','Door','AddrCate'),values=(floor[x],door[x],addrcate[x]))
                flash(u'%s 标准地址存在，修改成功'%address[x])
            else:
                addrid=insert(table='tAddr',fields=('Address','Floor','Door','AddrCate'),values=(address[x],floor[x],door[x],addrcate[x]))
                flash(u'%s 标准地址新增成功'%address[x])
            g.db.execute("replace into rAddrComm(AddrID,CommID) values(?,?)",(addrid,comm[x]))
            g.db.execute("replace into rAddrRes(AddrID,ResID) values(?,?)",(addrid,resid))
#            光站覆盖地址
            g.db.execute("replace into rAddrRes(AddrID,ResID) values(?,?)",(addrid,os[x]))
#            放大器覆盖地址
            if amp[x]<>'':
#                递归获取级联放大器的id，组成列表，然后再到这些放大器资源中绑定标准地址
                for ampid in amptree(amp[x]):
                    g.db.execute("replace into rAddrRes(AddrID,ResID) values(?,?)",(addrid,ampid))
            g.db.execute("replace into rAddrStat(AddrID,StatID) values(?,?)",(addrid,statid))
        g.db.commit()




    comms=query_db("select CommID,ProjectName as CommName from tComm left join tProject on tComm.Project=tProject.ProjectID where tComm.AccessType=3")
#    oss=query_db("select OSID,OSName from tRes where ")
#    oss=[]
    return render_template('dizhi.html',comms=comms)


def getStatID(installaddress, roomtype, comm):
    if roomtype:
        stat=query_db("select * from tStat where StationCode=? and Location=? and RoomType=? and Community=?",(installaddress,installaddress,roomtype,comm),one=True)
        if stat:
            statid=stat['StatID']
        else:
            statid=insert(table='tStat',fields=('StationCode','Location','RoomType','Community'),values=(installaddress,installaddress,roomtype,comm))
            flash(u'%s 资源箱体新增成功'%installaddress)
        return statid

@app.route('/res',methods=['GET','POST'])
@app.route('/res/<int:resid>',methods=['GET','POST'])
def res(resid=None):
    if request.method=="POST":
        action=request.form.get('action',None)
        comm=request.form.get('comm',None,type=int)
        parentres=request.form.get('parentres',None,type=int)
        installaddress=request.form.get('installaddress',None).strip()
        assetnumber=request.form.get('assetnumber',None)
        memo=request.form.get('memo',None)
        device=request.form.get('device',None,type=int)
        mac=request.form.get('mac',None)
        sn=request.form.get('sn',None)
        roomtype=request.form.get('roomtype',None)
        osport=request.form.get('osport',None)
        if osport=='':osport=None
        if installaddress=='':
            flash(u'安装地址不能为空')
            return redirect(url_for('res',resid=resid))

        statid=getStatID(installaddress,roomtype,comm)

        if action==u'保存':
#            g.db.execute("update tres set installaddress=?,assetnumber=?,memo=?,device=?,mac=?,sn=?,parentres=?,community=? where resid=?",(installaddress,assetnumber,memo,device,mac,sn,parentres,comm,resid))
            update(table='tRes',pk='ResID',pv=resid,fields=('InstallAddress','AssetNumber','Memo','Device','MAC','SN','ParentRes','Community','Station','OSPort','InstallDate'),values=(installaddress,assetnumber,memo,device,mac,sn,parentres,comm,statid,osport,datetime.strftime(datetime.now(),'%Y-%m-%d')))
            flash(u'%s 资源存在，修改成功'%installaddress)
            g.db.commit()
            return redirect(url_for('res',resid=resid))
        if action==u'删除':
            g.db.execute("delete from tres where resid=?",(resid,))
            g.db.commit()
            flash(u'%s 资源删除成功'%installaddress)
            return redirect(url_for('res'))
        if action==u'新增':
            resid=insert(table='tRes',fields=('InstallAddress','Assetnumber','Memo','Device','MAC','SN','ParentRes','Community','Station','OSPort','InstallDate'),values=(installaddress,assetnumber,memo,device,mac,sn,parentres,comm,statid,osport,datetime.strftime(datetime.now(),'%Y-%m-%d')))
            flash(u'%s 资源新增成功'%installaddress)
            return redirect(url_for('res',resid=resid))

    res=query_db("select * from vRes where ResID=?",(resid,),one=True)
    print resid,res
#    print 'res community',res['Community']
#    ress=query_db("select * from vres where Community=? and DevcTypeID in(4,11)",(res['Community'],))
#    print 'ress',ress
#    parentres=None
#    parentress=()
#    if res:
#        parentress=query_db("select * from vres where community=? and devctypeid in(1,2,3,4,5,11)",(res['Community'],))
#        if res['ParentRes']:
#            parentres=query_db("select * from tres where ParentRes=?",(res['ParentRes'],),one=True)
#    return render_template('res.html',res=res,parentres=parentres,parentress=parentress,comms=get_comms(),devices=get_devices(),resid=resid)
    roomtypes=query_db("select * from cRoomType where RoomTypeID in (1,2,3)")
    return render_template('res.html',res=res,comms=get_comms(),devices=get_devices(),roomtypes=roomtypes,resid=resid)

@app.route('/comm',methods=['GET','POST'])
@app.route('/comm/<int:commid>',methods=['GET','POST'])
def comm(commid=None):
    if request.method=="POST":
        action=request.form.get('action',None)
        projectid=request.form.get('projectid',None,type=int)
        projectname=request.form.get('projectname',None)
        areaid=request.form.get('areaid',None,type=int)
        installdate=datetime.strftime(datetime.now(),'%Y-%m-%d')
        if projectname.strip() =='':
            flash(u'小区名称不能为空')
            return redirect(url_for('comm',commid=commid))

        if action==u'保存':
            project=query_db("select * from tProject where ProjectName=?",(projectname,),one=True)
            if project:
                if projectid==project['ProjectID']:
                    g.db.execute("update tProject set AreaID=? where ProjectID=?",(areaid,projectid))
                    flash(u'修改片区成功')
                    g.db.commit()
#                    return redirect(url_for('comm',commid=commid))
                else:
                    projectid=project['ProjectID']
#                    g.db.execute("update tComm set Project=? where CommID=?",(areaid,commid))
#                    flash(u'修改小区名成功')
#                    g.db.commit()
#                    return redirect(url_for('comm',commid=commid))
                    catvcomm=query_db("select * from tComm where Project=? and AccessType=3",(projectid,),one=True)
                    if catvcomm:
                        commid=catvcomm['CommID']
                        flash(u'CATV小区已经存在')
                    else:
                        commid=insert(table='tComm',fields=('AccessType','InstallDate','Project'),values=(3,installdate,projectid))
                        flash(u'新增CATV小区成功')
    #                    return redirect(url_for('comm',commid=commid))
            else:
                projectid=insert(table='tProject',fields=('ProjectName','AreaID'),values=(projectname,areaid))
                flash(u'新增小区成功')

                print projectid
                commid=insert(table='tComm',fields=('AccessType','InstallDate','Project'),values=(3,installdate,projectid))
                flash(u'新增CATV小区成功')
            return redirect(url_for('comm',commid=commid))
        if action==u'新增':
            project=query_db("select * from tProject where ProjectName=?",(projectname,),one=True)
            print project
            if project:
                flash(u'小区已经存在')
                projectid=project['ProjectID']
                catvcomm=query_db("select * from tComm where Project=? and AccessType=3",(projectid,),one=True)
                if catvcomm:
                    flash(u'CATV小区已经存在')
                    commid=catvcomm['CommID']
#                    return redirect(url_for('comm',commid=commid))
                else:
                    commid=insert(table='tComm',fields=('AccessType','InstallDate','Project'),values=(3,installdate,projectid))
                    flash(u'新增CATV小区成功')
#                    return redirect(url_for('comm',commid=commid))

            else:
                projectid=insert(table='tProject',fields=('ProjectName','AreaID'),values=(projectname,areaid))
                flash(u'新增小区成功')
                print projectid
                commid=insert(table='tComm',fields=('AccessType','InstallDate','Project'),values=(3,installdate,projectid))
                flash(u'新增CATV小区成功')
            return redirect(url_for('comm',commid=commid))
        if action==u'删除':
            g.db.execute("delete from tComm where CommID=?",(commid,))
            g.db.commit()
#            commid=None
            flash(u'删除CATV小区成功')
            return redirect(url_for('comm'))

    comm=query_db("select * from vCatvComm where commid=?",(commid,),one=True)
    return render_template('comm.html',comm=comm,commid=commid,areas=get_areas())


@app.route('/searchres',methods=['GET'])
def searchres():
    sql="select * from pvRes where 1=1 and AccsTypeID=3"
    pc=25
    page=request.args.get('page',1,type=int)
    area=request.args.get('area',0,type=int)
    if area<>0:
        sql+=" and AreaID=%d"%area
    addr=request.args.get('addr',None)
    if addr:
        sql+=" and InstallAddress like '%%%s%%'"%addr
    commid=request.args.get('commid',0,type=int)
    comm=request.args.get('comm',None)
    if commid:
        sql+=" and Community=%d"%commid
    elif comm:
        sql+=" and CommunityName like '%%%s%%'"%comm

    sql+=" and DevcTypeID in (4,7,11)"
    print sql
    sqlrows="select count(*) as rows from (%s)"%sql
    #        print sqlrows
    rows=query_db(sqlrows,one=True)['rows']
    #        if rows%pc==0 and rows<>0:
    #            pages=rows//pc
    #        else:
    #            pages=rows//pc+1
    pages=int(ceil(rows/float(pc)))
    if page<=0:
        page=1
    if page>pages:
        page=pages
    offset=pc*(int(page)-1)

    sql+=" order by ResID desc"

    sql+=" limit %d offset %d"%(pc,offset)
    print sql
    ress=query_db(sql)

    return render_template('searchres.html',ress=ress,rows=rows,pages=pages,page=page)

@app.route('/topo')
def topo():
    return render_template('topo.html')

@app.route('/getNodes',methods=["POST"])
def getNodes():
    if request.method=="POST":
        id=request.form.get('id',0,type=int)
        nodeType=request.form.get('nodeType',None)
        if nodeType is None:
            nodes=query_db("select AreaID as id,AreaName as name,1 as isParent,'area' as nodeType, '/static/lib/jquery.zTree.v3.4/css/ztreestyle/img/diy/1_open.png' as icon from tArea")
        elif nodeType=='area':
            nodes=query_db("select CommID as id,ProjectName as name,1 as isParent,'comm' as nodeType,'/static/lib/jquery.zTree.v3.4/css/ztreestyle/img/diy/1_close.png' as icon from vCatvComm where AreaID=?",(id,))
        elif nodeType=='comm':
            nodes=query_db("select ResID as id,InstallAddress as name,1 as isParent,'os' as nodeType,'/static/lib/jquery.zTree.v3.4/css/ztreestyle/img/diy/3.png' as icon from vRes where devctypeid=4 and Community=?",(id,))
        elif nodeType=='os' or nodeType=='amp':
            nodes=query_db("select ResID as id,InstallAddress as name,case when devctypeid=11 then 1 else 0 end as isParent,case when devctypeid=11 then 'amp' else 'leaf' end as nodeType from vRes where parentres=?",(id,))

        print nodes
        return json.dumps(nodes)

@app.route('/getNodeInfo',methods=['GET'])
def getNodeInfo():
    if request.method=='GET':
        id=request.args.get('id',0,type=int)
        nodeType=request.args.get('nodeType',None)
        if nodeType in ['os','amp','leaf']:
            resinfo=query_db("select * from vRes where ResID=?",(id,),one=True)
            resphoto=query_db("select * from tResPhoto where ResourceID=?",(id,))
#            resstatis=query_db("select ")
            res={}
            res['resinfo']=resinfo
            res['resphoto']=resphoto
#            res['resstatis']=resstatis
            return json.dumps(res)


if __name__ == '__main__':
    app.run(host='0.0.0.0')